package com.example.excel.alibaba;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;


public class PoiCopyExcelSheet {
    /**
     * 测试方法
     *
     * @param args
     */
    public static void main(String[] args) {
        String fromPath = "D://excel/新版20.xls";// excel存放路径
        String toPath = "D:\\ok\\lulala.xls";// 保存新EXCEL路径
        InputStream in;
        try {
            in = new FileInputStream(fromPath);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            HSSFSheet sheet = wb.getSheetAt(1);
            HSSFWorkbook wbCreat = new HSSFWorkbook();
            HSSFSheet newSheet = wbCreat.createSheet(sheet.getSheetName());
            copySheets(newSheet, sheet, true);
            FileOutputStream fileOut = new FileOutputStream(toPath);
            wbCreat.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /**
     * 拷贝sheet
     *
     * @param newSheet  新的sheet
     * @param sheet     原有的sheet
     * @param copyStyle
     */
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) {
        int maxColumnNum = 0;
        Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
        MergerRegion(newSheet, sheet);
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow srcRow = sheet.getRow(i);
            HSSFRow destRow = newSheet.createRow(i);
            if (srcRow != null) {
                copyRow(sheet, newSheet, srcRow, destRow, styleMap);
                if (srcRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    }

    /**
     * 拷贝行
     *
     * @param srcSheet  原有的sheet
     * @param destSheet 新的sheet
     * @param srcRow    原有行
     * @param destRow   新的行
     * @param styleMap
     */
    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
        destRow.setHeight(srcRow.getHeight());
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            HSSFCell oldCell = srcRow.getCell(j);
            HSSFCell newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                copyCell(oldCell, newCell, styleMap);
                // MergerRegion(destSheet,srcSheet);
            }
        }

    }


    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     *
     * @param sheetCreat 新创建sheet
     * @param sheet      原有的sheet
     */
    private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {
        int rownum = sheet.getLastRowNum();
        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            oldRanges.add(sheet.getMergedRegion(i));
        }
        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (int k = 0; k < oldRanges.size(); k++) {
            CellRangeAddress oldRange = oldRanges.get(k);
            if (oldRange.getFirstRow() >= 0 && oldRange.getLastRow() <= rownum) {
                sheetCreat.addMergedRegion(oldRange);
            }
        }
    }


    /**
     * @param oldCell
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
        if (styleMap != null) {
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
                newCell.setCellStyle(oldCell.getCellStyle());
            } else {
                int stHashCode = oldCell.getCellStyle().hashCode();
                HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
                if (newCellStyle == null) {
                    newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                    styleMap.put(stHashCode, newCellStyle);
                }
                newCell.setCellStyle(newCellStyle);
            }
        }
        switch (oldCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:

                Object valuee = "";
                if (HSSFDateUtil.isCellDateFormatted(oldCell)) {
                    Date date = (Date) oldCell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    valuee = sdf.format(date);
                } else {
                    double value_temp = (double) oldCell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(value_temp);
                    BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
                    valuee = bd1.doubleValue();
                    DecimalFormat format = new DecimalFormat("#0.###");
                    valuee = format.format(oldCell.getNumericCellValue());
                }
                newCell.setCellValue(valuee.toString());

                //            newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                //       newCell.setCellFormula(oldCell.getCellFormula());
                try {
                    String value = String.valueOf(oldCell.getStringCellValue());
                    newCell.setCellValue(value);
                } catch (IllegalStateException e) {
                    String value = String.valueOf(oldCell.getNumericCellValue());
                    newCell.setCellValue(value);
                }
                break;
            default:
                break;
        }

    }
}





